﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using Dapper;
using BCCommon.Extensions;
using BCEntity.OA.BusinessTypeRecord;
using BCCommon;

namespace BCData.OA.BusinessTypeRecord
{
    public class BusinessTypeRecordData : IBusinessTypeRecordData
    {
        private const string QUERY = @"SELECT BusinessTypeRecordId,TypeName,TypeCode,AllowOperator,DepartmentFunction,CreateTime FROM OA_BusinessTypeRecord";

        private readonly IDatabaseContext databaseContext;

        public BusinessTypeRecordData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        /// <summary>
        /// 获取所有业务类型列表
        /// </summary>
        /// <returns></returns>
        public async Task<IEnumerable<BusinessTypeRecordEntity>> GetAllAsync()
        {
            return await databaseContext.Slave.QueryAsync<BusinessTypeRecordEntity>(QUERY);
        }

        /// <summary>
        /// 获取指定的业务类型
        /// </summary>
        /// <param name="typeName">业务类型名称</param>
        /// <param name="sobSettingId">账套Id</param>
        /// <returns></returns>
        public async Task<BusinessTypeRecordEntity> GetEntityByTypeNameAndSobSettingIdAsync(string typeName, long sobSettingId)
        {
            string sql = string.Format("{0} WHERE SobSettingId=?SobSettingId AND TypeName=?TypeName", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SobSettingId", sobSettingId, DbType.Int64);
            parameters.Add("?TypeName", typeName, DbType.AnsiString, size: 20);
            return await databaseContext.Slave.QueryFirstOrDefaultAsync<BusinessTypeRecordEntity>(sql, parameters);
        }

        /// <summary>
        /// 分页获取OA业务类型记录(列表)
        /// </summary>
        /// <param name="sobSetting"></param>
        /// <param name="start">创建时间开始</param>
        /// <param name="end">创建时间结束</param>
        /// <param name="ascending">是否升序排序</param>
        /// <param name="pageIndex">分页页码</param>
        /// <param name="pageSize">分页页数</param>
        /// <returns></returns>
        public async Task<(IEnumerable<BusinessTypeRecordEntity>, int)> GetListAsync(string typeName, DateTime? start, DateTime? end, bool? ascending, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
           
            if (typeName.IsNotNull())
            {
                wheres.Add(" TypeName LIKE CONCAT('%',CONCAT(?TypeName,'%'))");
                parameters.Add("?TypeName", typeName, DbType.AnsiString, size: 20);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add(" CreateTime>=DATE_ADD(?Start,INTERVAL 1 DAY)");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add(" CreateTime<=DATE_ADD(?End,INTERVAL 1 DAY)");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime>=DATE_ADD(?Start,INTERVAL 1 DAY) AND  CreateTime<=DATE_ADD(?End,INTERVAL 1 DAY)");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }

            string querySql = string.Format(@"{0} {1} ORDER BY CreateTime {4} LIMIT {2},{3}; SELECT COUNT(1) FROM  OA_BusinessTypeRecord {1}",
               QUERY,
               wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
               (pageIndex - 1) * pageSize, pageSize,
               ascending.HasValue && ascending.Value ? "ASC" : "DESC");

            return await databaseContext.Slave.QueryMultipleAsync<BusinessTypeRecordEntity,int>(querySql, parameters);
        }

        /// <summary>
        /// 新增OA业务类型
        /// </summary>
        /// <param name="entity">业务类型信息</param>
        /// <returns></returns>
        public async Task<BusinessTypeRecordEntity> InsertAsync(BusinessTypeRecordEntity entity)
        {
            string sql = SqlCommands.GetInsertSql("OA_BusinessTypeRecord", new string[] { "SobSettingId", "CashierTransportAccountCertificateSettingId", "TypeName", "TypeCode", "AllowOperator", "DepartmentFunction" });

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SobSettingId", entity.SobSettingId, DbType.Int64);
            parameters.Add("?CashierTransportAccountCertificateSettingId", entity.CashierTransportAccountCertificateSettingId, DbType.Int64);
            parameters.Add("?TypeName", entity.TypeName, DbType.AnsiString, size: 20);
            parameters.Add("?TypeCode", entity.TypeCode, DbType.AnsiString, size: 100);
            parameters.Add("?AllowOperator", entity.AllowOperator.ToString(), DbType.AnsiString);
            parameters.Add("?DepartmentFunction", entity.DepartmentFunction, DbType.AnsiString, size: 150);
            string querySql = string.Format("{0} {1} Where BusinessTypeRecordId=LAST_INSERT_ID();", sql, QUERY);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<BusinessTypeRecordEntity>(querySql, parameters, databaseContext.Transaction);
        }

        /// <summary>
        /// 修改OA业务类型
        /// </summary>
        /// <param name="entity">业务类型信息</param>
        /// <returns></returns>
        public async Task<BusinessTypeRecordEntity> UpdateAsync(BusinessTypeRecordEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("OA_BusinessTypeRecord", new string[] { "TypeName", "TypeCode", "AllowOperator", "DepartmentFunction" }, new string[] { "BusinessTypeRecordId" });

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?BusinessTypeRecordId", entity.BusinessTypeRecordId, DbType.Int64);
            parameters.Add("?TypeName", entity.TypeName, DbType.AnsiString, size: 20);
            parameters.Add("?TypeCode", entity.TypeCode, DbType.AnsiString, size: 100);
            parameters.Add("?AllowOperator", entity.AllowOperator.ToString(), DbType.AnsiString);
            parameters.Add("?DepartmentFunction", entity.DepartmentFunction.ToString(), DbType.AnsiString, size: 150);
            string querySql = string.Format("{0} {1} Where BusinessTypeRecordId=?BusinessTypeRecordId;", sql, QUERY);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<BusinessTypeRecordEntity>(querySql, parameters, databaseContext.Transaction);
        }

        /// <summary>
        /// 校验是否已存在
        /// </summary>
        /// <param name="typeName"></param>
        /// <returns></returns>
        public bool CheckIsExists(string typeName, long sobSettingId)
        {
            string sql = "SELECT 1 FROM OA_BusinessTypeRecord WHERE TypeName=?TypeName AND  SobSettingId=?SobSettingId LIMIT 0,1;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TypeName", typeName, DbType.AnsiString, size: 20);
            parameters.Add("?SobSettingId", sobSettingId, DbType.Int64, size: 20);
            return this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters).Result != null;
        }

        /// <summary>
        /// 校验是否已存在
        /// </summary>
        /// <param name="businessTypeRecordId"></param>
        /// <returns></returns>
        public bool CheckIsExists(long businessTypeRecordId)
        {
            string sql = "SELECT COUNT(1) FROM OA_BusinessTypeRecord WHERE BusinessTypeRecordId=?BusinessTypeRecordId LIMIT 0,1;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?BusinessTypeRecordId", businessTypeRecordId, DbType.Int64);
            return this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters).Result != null;
        }
    }
}
